package com.web.hotel.dao;

import com.util.LogUtil;
import com.util.SqlUtil;
import com.web.hotel.po.Hotel;
import com.web.hotel.po.mapper.HotelMapper;
import com.web.hotel.service.HotelService;
import com.web.hotel.vo.HotelVo;
import com.web.hotel.vo.mapper.HotelVoMapper;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.orm.hibernate4.HibernateTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.util.*;

@Service(value = "hotelService")
@Transactional(rollbackFor = {Exception.class})
public class HotelDAO implements HotelService {

    @Resource
    SessionFactory factory;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private HibernateTemplate hibernateTemplate;

    public Map<String, Object> listGrid(HttpServletRequest request) {
        List<HotelVo> hotelList = new ArrayList<HotelVo>();
        Map<String, Object> dataMap = new HashMap<String, Object>();
        Session s = null;
        /**
         * 接受客户端传递的排序参数
         *
         */
        String sortOrder = request.getParameter("order");
        String sortField = request.getParameter("sort");

        /**
         * 接受客户端传递的分页参数
         *
         */
        String currentPage = request.getParameter("page");
        String pageSize = request.getParameter("rows");
        if (currentPage == null || currentPage.equals("")) {
            currentPage = "1";
        }
        if (pageSize == null || pageSize.equals("")) {
            pageSize = "10";
        }
        int int_currentPage = Integer.parseInt(currentPage);
        int int_pageSize = Integer.parseInt(pageSize);

        int startRow = (int_currentPage - 1) * int_pageSize;
        int rsCount = 0;


        String sql =
                "SELECT\n" +
                        "	h.hotelId,\n" +
                        "	h.hotelName,\n" +
                        "	h.hotelCode,\n" +
                        "	h.hotelDesc,\n" +
                        "	h.hotelImg,\n" +
                        "	h.hotelType,\n" +
                        "	c.cityName,\n" +
                        "	c.landmarks,\n" +
                        "	c.areaName\n" +
                        "FROM\n" +
                        "	t_hotel h\n" +
                        "LEFT JOIN t_city c ON h.cityId = c.cityId\n" +
                        "where 1=1";
        List<Object> param = new ArrayList<Object>();
        /**
         * 接收客户端的查询参数
         *
         */
        String param_hotelName = request.getParameter("param_hotelName");
        String param_hotelCode = request.getParameter("param_hotelCode");
        String param_hotelDesc = request.getParameter("param_hotelDesc");
        String param_hotelType = request.getParameter("param_hotelType");
        String param_city = request.getParameter("param_cityName");

        StringBuffer querySQL = new StringBuffer();
        StringBuffer countSQL = new StringBuffer();
        StringBuffer whereSQL = new StringBuffer();

        if (param_hotelName != null && !param_hotelName.equals("")) {
            sql += "t.hotelName like ?";
            param.add("%" + param_hotelName + "%");
        }
        if (param_hotelCode != null && !param_hotelCode.equals("")) {
            sql += "t.hotelCode like ?";
            param.add("%" + param_hotelCode + "%");
        }
        if (param_hotelDesc != null && !param_hotelDesc.equals("")) {
            sql += "t.hotelDesc like ?";
            param.add("%" + param_hotelDesc + "%");
        }
        if (param_hotelType != null && !param_hotelType.equals("")) {
            sql += "t.hotelType like ?";
            param.add("%" + param_hotelType + "%");
        }
        if (param_city != null && !param_city.equals("")) {
            sql += "t.cityName like ?";
            param.add("%" + param_city + "%");
        }
        /**
         * 1.先取出总记录数
         */
        String countSql = SqlUtil.getCountSql(sql);
        String pageSql = sql;
        if (StringUtils.isNotEmpty(sortField)) {
            pageSql += " order by " + sortField + " " + sortOrder + "";
        } else {
            pageSql += " order by hotelId asc";
        }
        /**
         * 2.产生分页语句
         */
        pageSql = SqlUtil.getMysqlPageSql(pageSql, Integer.parseInt(currentPage), Integer.parseInt(pageSize));

        try {
            LogUtil.sqlLog("总记录数", countSql);
            LogUtil.sqlLog("酒店分页", pageSql);
            rsCount = jdbcTemplate.queryForObject(countSql, Integer.class, param.toArray());
            hotelList = jdbcTemplate.query(pageSql, param.toArray(), new HotelVoMapper());
        } catch (Exception e) {
            e.printStackTrace();
        }
        dataMap.put("total", rsCount);
        dataMap.put("rows", hotelList);
        return dataMap;
    }


    public Map<String, Object> deleteHotelById(String hotelIds) {
        Map<String, Object> dataMap = new HashMap<String, Object>();
        Session s = null;
        StringBuffer deleteSQL = new StringBuffer();
        deleteSQL.append("delete ");
        deleteSQL.append(" from t_hotel where hotelId in");
        deleteSQL.append("(" + hotelIds + ")");

        try {
            s = factory.getCurrentSession();
            int count = s.createSQLQuery(deleteSQL.toString()).executeUpdate();
            dataMap.put("flag", true);
            dataMap.put("msg", "成功删除" + count + "条数据");
        } catch (Exception e) {
            e.printStackTrace();
            dataMap.put("flag", false);
            dataMap.put("msg", e.getMessage());
        }
        return dataMap;
    }

    public Map<String, Object> addHotel(Hotel hotel) {
        Map<String, Object> dataMap = new HashMap<String, Object>();
        if (hotel == null) {
            dataMap.put("flag", false);
            dataMap.put("msg", "服务端未接收到客户端传递的数据");
            return dataMap;
        }
        try {
            factory.getCurrentSession().save(hotel);
            dataMap.put("flag", true);
            dataMap.put("msg", "保存酒店成功,继续添加？");
        } catch (Exception e) {
            e.printStackTrace();
            dataMap.put("flag", false);
            dataMap.put("msg", e.getMessage());
        }

        return dataMap;
    }

    public Hotel getHotelById(Integer hotelId) {
        List<Object> params = new ArrayList<Object>();
        String sql = "select * from t_hotel where 1=1 ";
        if (hotelId != null) {
            sql += " AND hotelId = ? ";
            params.add(hotelId);
        }
        LogUtil.sqlLog("根据ID查询酒店", sql);
        return jdbcTemplate.queryForObject(sql, params.toArray(), new HotelMapper());
    }

    public HotelVo getHotelVoById(Integer hotelId) {
        List<Object> params = new ArrayList<Object>();
        String sql =
                "SELECT\n" +
                        "	h.hotelId,\n" +
                        "	h.hotelName,\n" +
                        "	h.hotelCode,\n" +
                        "	h.hotelImg,\n" +
                        "	h.hotelType,\n" +
                        "	c.cityName,\n" +
                        "	c.landmarks,\n" +
                        "	c.areaName,\n" +
                        "	h.hotelDesc\n" +
                        "FROM\n" +
                        "	t_hotel h\n" +
                        "LEFT JOIN t_city c ON h.cityId = c.cityId\n" +
                        "WHERE\n" +
                        "	h.hotelId = ?";
        params.add(hotelId);
        LogUtil.sqlLog("根据ID查询酒店", sql);
        return jdbcTemplate.queryForObject(sql, params.toArray(), new HotelVoMapper());
    }

    public Map<String, Object> editHotel(HotelVo hotelVo) {
        Map<String, Object> dataMap = new HashMap<String, Object>();
        List<Object> params = new ArrayList<Object>();
        try {
            String landmarks = hotelVo.getLandmarks();
            String cityName = hotelVo.getCityName();
            String areaName = hotelVo.getAreaName();
            String sql =
                    "SELECT\n" +
                            "	cityId\n" +
                            "FROM\n" +
                            "	t_city\n" +
                            "WHERE\n" +
                            "	1 = 1\n";
            if (StringUtils.isNoneEmpty(landmarks)) {
                sql += " AND landmarks = ?";
                params.add(landmarks);
            }
            if (StringUtils.isNoneEmpty(cityName)) {
                sql += " AND cityName = ?";
                params.add(cityName);
            }
            if (StringUtils.isNoneEmpty(areaName)) {
                sql += " AND areaName = ?";
                params.add(areaName);
            }
            LogUtil.sqlLog("查询城市ID", sql);
            int cityId = jdbcTemplate.queryForObject(sql, params.toArray(), Integer.class);
            String updateSQL =
                    "UPDATE t_hotel\n" +
                            "SET hotelName = ?,\n" +
                            " cityId = ?,\n" +
                            " hotelCode = ?,\n" +
                            " hotelDesc = ?,\n" +
                            " hotelImg = ?,\n" +
                            " hotelType = ?,\n" +
                            " createTime = ?,\n" +
                            " createUser = ?\n" +
                            "WHERE\n" +
                            "	hotelId = ?";
            LogUtil.sqlLog("修改酒店信息", updateSQL);
            List<Object> updateParams = new ArrayList<Object>();
            updateParams.add(hotelVo.getHotelName());
            updateParams.add(cityId);
            updateParams.add(hotelVo.getHotelCode());
            updateParams.add(hotelVo.getHotelDesc());
            updateParams.add(hotelVo.getHotelImg());
            updateParams.add(hotelVo.getHotelType());
            updateParams.add(new Date());
            updateParams.add("admin");
            updateParams.add(hotelVo.getHotelId());
            jdbcTemplate.update(updateSQL, updateParams.toArray());
            dataMap.put("flag", true);
            dataMap.put("msg", "修改成功，确认保存？");
        } catch (Exception e) {
            e.printStackTrace();
            dataMap.put("flag", false);
            dataMap.put("msg", e.getMessage());
        }
        return dataMap;
    }


    public List<Hotel> getAllHotelByName(String hotelName) {
        List<Hotel> hotelList = new ArrayList<Hotel>();
        Session s = null;
        StringBuffer querySQL = new StringBuffer();
        StringBuffer whereSQL = new StringBuffer();
        if (hotelName != null && !hotelName.equals("")) {
            whereSQL.append(" and hotelName like '%" + hotelName + "%'");
        }
        querySQL.append("from Hotel h");
        querySQL.append(" where 1 = 1");
        if (whereSQL.length() > 0) {
            querySQL.append(whereSQL);
        }
        querySQL.append(" order by hotelName asc");
        try {
            s = factory.getCurrentSession();
            Query query = s.createQuery(querySQL.toString());
            query.setFirstResult(0);
            query.setMaxResults(11);
            hotelList = query.list();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return hotelList;
    }


    public List<Hotel> getAllHotelByCityName(String cityName) {
        List<Hotel> hotelList = new ArrayList<Hotel>();
        List<String> params = new ArrayList<String>();
        String sql = "select * from t_hotel where 1=1 ";
        if (StringUtils.isNoneEmpty(cityName)) {
            sql += " and cityName like ?";
            params.add("%" + cityName + "%");
        }
        sql += " order by cityName asc";
        try {
            hotelList = jdbcTemplate.query(sql, params.toArray(), new HotelMapper());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return hotelList;
    }


}
